Calculate Corellations for 5 Stocks/Calculatethe Correlation of the Returns for Stock Pairs.

Using Data from the Internet (finance.yahoo.com)
1. Go to Finance Yahoo! on the internet at URL, http://finance.yahoo.com/

2. Enter the ticker symbol for the first stock (this gives a recent quote of the stock price).

3. To the left of the stock price quote box, press historical quotes (this gives you access to the raw price data for the stock).

4. Choose Daily, Weekly, or Monthly data on the radio buttons.

5. Enter the start date and end date for the stock price data that you want (or just accept the default data range), and then press Get Prices.

6. Press “Download to Spreadsheet”, which is at the bottom of the price table. (This retrieves the stock price data requested).

7. The next screen is a panel to save the data. Just open with Microsoft Excel to see the data within a spreadsheet.

8. I usually delete the columns titled Open, High, Low, Close, and Volume because all we normally need is the Adjusted Close, which corrects for dividends and stock splits. Then I usually add a few empty rows at the top of the table, and title the Date and Adjusted Close columns with the ticker symbol of the first stock.

9. Return to Yahoo!Finance where you downloaded the historical stock data and change the ticker symbol to the second stock after “Get Historical Prices for:” and then press “GO”. This should retrieve stock prices for the second stock in the same time period as for the first stock you chose.

10. Once again at the bottom of this page, press Download Spreadsheet Format and openwith Excel. I usually delete the columns titled Open, High, Low, Close, and Volume again.

11. Now copy the two columns titled Date and Adjusted Close, and paste in the spreadsheet that contains data for the first stock, but leave 3 blank columns between the data, and put the ticker symbol for the second stock above the new date and adjusted closing price columns so you can identify the data for the second stock.

12. Now you need to look at your data. Check the first data point. If you are using monthly or weekly data, the first point might not be a complete period. For example, if you are retrieving monthly data on September 22, the month is not really completed. Yahoo Finance reports the last price so far in the month rather than the ending month price. So you need to delete that first row of data if it is not complete.

13. Now calculate the returns for the adjusted closing prices for the two stocks in the column just to the right of the adjusted closing prices. The formula is (Pricetoday – Priceyesterday)/Priceyesterday. Place this formula to the right of the closing price data for both stocks.

14. Now you can calculate the correlation of the returns for the two stocks. In a cell above the tables, type in the formula “=CORREL(”, and then the function will open in a window to show that you need to enter Array1 and Array2, separated by commas.I usually highlight the returns from the first return for the first stock down the column until I have the amount of data I want to use. Then press a comma, and the same amount of returns for the second stock. Finally, press the closing parenthesis “)” and Enter to get the Correlation Coefficient. As to the amount of data to use, if it is monthly data, the normal amount is 60 returns (or 5 years). For weekly data, we often use two years of weeks, or 100 returns; and for daily data, we often use one year of daily returns, or about 252 returns.

15. Your assignment is to calculate a correlation matrix for 5 stocks. This means you will have to collect adjusted closing price data for 5 different stocks (see above process) and then calculate the correlation coefficient for each possible pair. With 5 stocks there are a total of 10 different unique correlation coefficient calculations. If we choose Exxon-Mobil, IBM, Kroger, Apple, and Caterpillar the table should look something like the following:

XOM IBM KR AAPL CAT
XOM 1
IBM XXXXXX 1
KR XXXXXX XXXXXX 1
AAPL XXXXXX XXXXXX XXXXXX 1
CAT XXXXXX XXXXXX XXXXXX XXXXXX 1
Note that only the lower left portion of the table has numbers. This is because the numbers in the top right portion are just repeats of the same numbers in the lower left of the table. In other words, CORREL(A,B) = CORREL(B,A). Also note that the diagonal correlations are all equal to 1. This is because the correlation of a variable with itself is always a correlation equal to 1, or CORREL(A,A) = 1.

Calculate Corellations for 5 Stocks/Calculatethe Correlation of the Returns for Stock Pairs.

Using Data from the Internet (finance.yahoo.com)
1. Go to Finance Yahoo! on the internet at URL, http://finance.yahoo.com/

2. Enter the ticker symbol for the first stock (this gives a recent quote of the stock price).

3. To the left of the stock price quote box, press historical quotes (this gives you access to the raw price data for the stock).

4. Choose Daily, Weekly, or Monthly data on the radio buttons.

5. Enter the start date and end date for the stock price data that you want (or just accept the default data range), and then press Get Prices.

6. Press “Download to Spreadsheet”, which is at the bottom of the price table. (This retrieves the stock price data requested).

7. The next screen is a panel to save the data. Just open with Microsoft Excel to see the data within a spreadsheet.

8. I usually delete the columns titled Open, High, Low, Close, and Volume because all we normally need is the Adjusted Close, which corrects for dividends and stock splits. Then I usually add a few empty rows at the top of the table, and title the Date and Adjusted Close columns with the ticker symbol of the first stock.

9. Return to Yahoo!Finance where you downloaded the historical stock data and change the ticker symbol to the second stock after “Get Historical Prices for:” and then press “GO”. This should retrieve stock prices for the second stock in the same time period as for the first stock you chose.

10. Once again at the bottom of this page, press Download Spreadsheet Format and openwith Excel. I usually delete the columns titled Open, High, Low, Close, and Volume again.

11. Now copy the two columns titled Date and Adjusted Close, and paste in the spreadsheet that contains data for the first stock, but leave 3 blank columns between the data, and put the ticker symbol for the second stock above the new date and adjusted closing price columns so you can identify the data for the second stock.

12. Now you need to look at your data. Check the first data point. If you are using monthly or weekly data, the first point might not be a complete period. For example, if you are retrieving monthly data on September 22, the month is not really completed. Yahoo Finance reports the last price so far in the month rather than the ending month price. So you need to delete that first row of data if it is not complete.

13. Now calculate the returns for the adjusted closing prices for the two stocks in the column just to the right of the adjusted closing prices. The formula is (Pricetoday – Priceyesterday)/Priceyesterday. Place this formula to the right of the closing price data for both stocks.

14. Now you can calculate the correlation of the returns for the two stocks. In a cell above the tables, type in the formula “=CORREL(”, and then the function will open in a window to show that you need to enter Array1 and Array2, separated by commas.I usually highlight the returns from the first return for the first stock down the column until I have the amount of data I want to use. Then press a comma, and the same amount of returns for the second stock. Finally, press the closing parenthesis “)” and Enter to get the Correlation Coefficient. As to the amount of data to use, if it is monthly data, the normal amount is 60 returns (or 5 years). For weekly data, we often use two years of weeks, or 100 returns; and for daily data, we often use one year of daily returns, or about 252 returns.

15. Your assignment is to calculate a correlation matrix for 5 stocks. This means you will have to collect adjusted closing price data for 5 different stocks (see above process) and then calculate the correlation coefficient for each possible pair. With 5 stocks there are a total of 10 different unique correlation coefficient calculations. If we choose Exxon-Mobil, IBM, Kroger, Apple, and Caterpillar the table should look something like the following:

XOM IBM KR AAPL CAT
XOM 1
IBM XXXXXX 1
KR XXXXXX XXXXXX 1
AAPL XXXXXX XXXXXX XXXXXX 1
CAT XXXXXX XXXXXX XXXXXX XXXXXX 1
Note that only the lower left portion of the table has numbers. This is because the numbers in the top right portion are just repeats of the same numbers in the lower left of the table. In other words, CORREL(A,B) = CORREL(B,A). Also note that the diagonal correlations are all equal to 1. This is because the correlation of a variable with itself is always a correlation equal to 1, or CORREL(A,A) = 1.